﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class LichThiDau_DAO
    {
        //Lấy danh sách cầu thủ
        static public DataTable SelectAll()
        {
            string sql = "select * from LICHTHIDAU";
            return DataAccess.executeQuery(sql);
        }
        public static DataTable SelectbyDoiBong(int id)
        {
            string sql = "select * from LICHTHIDAU where Doi1 = " + id + "or Doi2 = " + id + "";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static LichThiDau_DTO SelectbyDoiBong(int id1, int id2)
        {
            string sql = "select * from LICHTHIDAU where (Doi1 = " + id1 + " and Doi2 = " + id2 +
                                                    ") or (Doi1 = " + id2 + " and Doi2 = " + id1 + ")";
            DataTable dt = DataAccess.executeQuery(sql);
            LichThiDau_DTO dto = new LichThiDau_DTO();
            dto.Doi1 = Convert.ToInt32(dt.Rows[0]["Doi1"]);
            dto.Doi2 = Convert.ToInt32(dt.Rows[0]["Doi2"]);
            dto.VongDau = Convert.ToInt32(dt.Rows[0]["VongDau"]);
            dto.Ngay = Convert.ToDateTime(dt.Rows[0]["Ngay"]);
            dto.Gio = Convert.ToString(dt.Rows[0]["Gio"]);
            dto.San = Convert.ToInt32(dt.Rows[0]["San"]);
            dto.BanThang = Convert.ToInt32(dt.Rows[0]["BanThang"]);
            dto.BanThua = Convert.ToInt32(dt.Rows[0]["BanThua"]);
            dto.TtChinh = Convert.ToInt32(dt.Rows[0]["TrongTaiChinh"]);
            dto.TtBien1 = Convert.ToInt32(dt.Rows[0]["TrongTaiBien1"]);
            dto.TtBien2 = Convert.ToInt32(dt.Rows[0]["TrongTaiBien2"]);
            dto.TtBan = Convert.ToInt32(dt.Rows[0]["TrongTaiBan"]);
            return dto;
        }
        public static DataTable SelectbyVong(int id)
        {
            string sql = "select * from LICHTHIDAU where VongDau = " + id + "";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        
        public static LichThiDau_DTO SelectByID(int id)
        {
            string sql = "select * from LICHTHIDAU where MaTranDau = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            LichThiDau_DTO dto = new LichThiDau_DTO();
            dto.Doi1 = Convert.ToInt32(dt.Rows[0]["Doi1"]);
            dto.Doi2 = Convert.ToInt32(dt.Rows[0]["Doi2"]);
            dto.VongDau = Convert.ToInt32(dt.Rows[0]["VongDau"]);
            dto.Ngay = Convert.ToDateTime(dt.Rows[0]["Ngay"]);
            dto.Gio = Convert.ToString(dt.Rows[0]["Gio"]);
            dto.San = Convert.ToInt32(dt.Rows[0]["San"]);
            dto.BanThang = Convert.ToInt32(dt.Rows[0]["BanThang"]);
            dto.BanThua = Convert.ToInt32(dt.Rows[0]["BanThua"]);
            dto.TtChinh = Convert.ToInt32(dt.Rows[0]["TrongTaiChinh"]);
            dto.TtBien1 = Convert.ToInt32(dt.Rows[0]["TrongTaiBien1"]);
            dto.TtBien2 = Convert.ToInt32(dt.Rows[0]["TrongTaiBien2"]);
            dto.TtBan = Convert.ToInt32(dt.Rows[0]["TrongTaiBan"]);
            return dto;
        }
        
        public static void Insert(LichThiDau_DTO dto)
        {
            string sql = "Insert INTO LICHTHIDAU (VongDau, Doi1, Doi2, Ngay, Gio, San, BanThang,"
             + "BanThua, TrongTaiChinh, TrongTaiBien1, TrongTaiBien2, TrongTaiBan ) VALUES (" +
                                dto.VongDau +
                                "," + dto.Doi1 +
                                "," + dto.Doi2 +
                                ",'" + dto.Ngay +
                                "','" + dto.Gio +
                                "'," + dto.San +
                                "," + dto.BanThang +
                                "," + dto.BanThua +
                                "," + dto.TtChinh +
                                "," + dto.TtBien1 +
                                "," + dto.TtBien2 +
                                "," + dto.TtBan + ")";

            DataAccess.executeNonQuery(sql);
        }

        public static void Update(LichThiDau_DTO dto)
        {
            string sql = "Update LICHTHIDAU set VongDau=(" + dto.VongDau +
                                            "),Doi1=(" + dto.Doi1 +
                                            "),Doi2=(" + dto.Doi2 +
                                            "),Ngay=('" + dto.Ngay +
                                            "'),Gio=('" + dto.Gio +
                                            "'),San=(" + dto.San +
                                            "),BanThang=(" + dto.BanThang +
                                            "),BanThua=(" + dto.BanThua +
                                            "),TrongTaiChinh=(" + dto.TtChinh +
                                            "),TrongTaiBien1=(" + dto.TtBien1 +
                                            "),TrongTaiBien2=(" + dto.TtBien2 +
                                            "),TrongTaiBan=(" + dto.TtBan +
                                            ") where MaTranDau = " + dto.MaTranDau + "";

            DataAccess.executeNonQuery(sql);
        }
        public static void Delete(int ma)
        {
            string sql = "Delete from LICHTHIDAU where MaTranDau = " + ma + "";
            DataAccess.executeNonQuery(sql);
        }
        public static void Delete()
        {
            string sql = "Delete from LICHTHIDAU";
            DataAccess.executeNonQuery(sql);
        }
    }
}
